<!DOCTYPE html>
<html lang="en">





<head>
  <meta charset="UTF-8">
  <link rel="apple-touch-icon" sizes="76x76" href="/img/favicon.jpg">
  <link rel="icon" type="image/png" href="/img/favicon.jpg">
  <meta name="viewport"
        content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no, shrink-to-fit=no">
  <meta http-equiv="x-ua-compatible" content="ie=edge">
  
  <meta name="theme-color" content="#2f4154">
  <meta name="description" content="">
  <meta name="author" content="John Doe">
  <meta name="keywords" content="">
  <title>MySQL高级 - Nyima&#39;s Blog</title>

  <link  rel="stylesheet" href="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/css/bootstrap.min.css" />


  <link  rel="stylesheet" href="https://cdn.staticfile.org/github-markdown-css/4.0.0/github-markdown.min.css" />
  <link  rel="stylesheet" href="/lib/hint/hint.min.css" />

  
    <link  rel="stylesheet" href="https://cdn.staticfile.org/highlight.js/10.0.0/styles/github-gist.min.css" />
  

  


<!-- 主题依赖的图标库，不要自行修改 -->

<link rel="stylesheet" href="//at.alicdn.com/t/font_1749284_yg9cfy8wd6.css">



<link rel="stylesheet" href="//at.alicdn.com/t/font_1736178_pjno9b9zyxs.css">


<link  rel="stylesheet" href="/css/main.css" />

<!-- 自定义样式保持在最底部 -->


  <script  src="/js/utils.js" ></script>
<meta name="generator" content="Hexo 4.2.1"></head>


<body>
  <header style="height: 70vh;">
    <nav id="navbar" class="navbar fixed-top  navbar-expand-lg navbar-dark scrolling-navbar">
  <div class="container">
    <a class="navbar-brand"
       href="/">&nbsp;<strong>Nyima</strong>&nbsp;</a>

    <button id="navbar-toggler-btn" class="navbar-toggler" type="button" data-toggle="collapse"
            data-target="#navbarSupportedContent"
            aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
      <div class="animated-icon"><span></span><span></span><span></span></div>
    </button>

    <!-- Collapsible content -->
    <div class="collapse navbar-collapse" id="navbarSupportedContent">
      <ul class="navbar-nav ml-auto text-center">
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/">
                <i class="iconfont icon-home-fill"></i>
                Home
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/archives/">
                <i class="iconfont icon-archive-fill"></i>
                Archives
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/categories/">
                <i class="iconfont icon-category-fill"></i>
                Categories
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/tags/">
                <i class="iconfont icon-tags-fill"></i>
                Tags
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/links/">
                <i class="iconfont icon-link-fill"></i>
                Links
              </a>
            </li>
          
        
          
          
          
          
            <li class="nav-item">
              <a class="nav-link" href="/about/">
                <i class="iconfont icon-user-fill"></i>
                About
              </a>
            </li>
          
        
        
          <li class="nav-item" id="search-btn">
            <a class="nav-link" data-toggle="modal" data-target="#modalSearch">&nbsp;&nbsp;<i
                class="iconfont icon-search"></i>&nbsp;&nbsp;</a>
          </li>
        
      </ul>
    </div>
  </div>
</nav>

    <div class="view intro-2" id="background" parallax=true
         style="background: url('/img/3.jpg') no-repeat center center;
           background-size: cover;">
      <div class="full-bg-img">
        <div class="mask flex-center" style="background-color: rgba(0, 0, 0, 0.3)">
          <div class="container text-center white-text fadeInUp">
            <span class="h2" id="subtitle">
              
            </span>

            
              
  <div class="mt-3 post-meta">
    <i class="iconfont icon-date-fill" aria-hidden="true"></i>
    <time datetime="2020-08-16 10:00">
      August 16, 2020 am
    </time>
  </div>


<div class="mt-1">
  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-chart"></i>
      12.9k 字
    </span>
  

  
    
    <span class="post-meta mr-2">
      <i class="iconfont icon-clock-fill"></i>
      
      
      160
       分钟
    </span>
  

  
  
</div>

            
          </div>

          
        </div>
      </div>
    </div>
  </header>

  <main>
    
      

<div class="container-fluid">
  <div class="row">
    <div class="d-none d-lg-block col-lg-2"></div>
    <div class="col-lg-8 nopadding-md">
      <div class="container nopadding-md" id="board-ctn">
        <div class="py-5" id="board">
          <div class="post-content mx-auto" id="post">
            
            <article class="markdown-body">
              <p>该博客是根据<a href="https://www.bilibili.com/video/BV1KW411u7vy?p=9" target="_blank" rel="noopener"><strong>尚硅谷MySQL数据库高级，mysql优化，数据库优化</strong></a>学习时，所做的笔记</p>
<p>同时又参考了<a href="https://www.bilibili.com/video/av541567925?p=1" target="_blank" rel="noopener"><strong>2020最新完整版MYSQL高级教程</strong></a></p>
<h1 id="MySQL高级"><a href="#MySQL高级" class="headerlink" title="MySQL高级"></a>MySQL高级</h1><h1 id="一、Mysql逻辑架构"><a href="#一、Mysql逻辑架构" class="headerlink" title="一、Mysql逻辑架构"></a>一、Mysql逻辑架构</h1><h2 id="1、整体架构"><a href="#1、整体架构" class="headerlink" title="1、整体架构"></a>1、整体架构</h2><p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200813170808.png" srcset="/img/loading.gif" alt=""></p>
<h3 id="连接层"><a href="#连接层" class="headerlink" title="连接层"></a>连接层</h3><p>最上层是一些客服端和连接服务，包括socket通信和大多数基于客服端/服务端工具实现的类似于tcp/ip的通信，主要完成一些类似于连接处理、授权认证及相关安全的方案，在该层上引入了线程池的概念，为通过认证安全接入的客服端提供线程，同样在该层上可以实现基于SSL的安全的连接，服务器也会为安全接入的每个客户端验证它所具有的操作权限</p>
<h3 id="服务层"><a href="#服务层" class="headerlink" title="服务层"></a>服务层</h3><p>第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析以及优化部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询的顺序是否利用索引，最后生成相应的执行操作</p>
<table>
<thead>
<tr>
<th>Management Serveices &amp; Utilities</th>
<th>系统管理和控制工具</th>
</tr>
</thead>
<tbody><tr>
<td>SQL Interface</td>
<td>SQL 接口。接受用户的 SQL 命令，并且返回用户需要查询的结果。比如 select from 就是调用 SQL Interface</td>
</tr>
<tr>
<td>Parser</td>
<td>解析器。 SQL 命令传递到解析器的时候会被解析器验证和解析</td>
</tr>
<tr>
<td>Optimizer</td>
<td>查询优化器。 SQL 语句在查询之前会使用查询优化器对查询进行优化，比如有 where 条件时，优化器来决定先投影还是先过滤。</td>
</tr>
<tr>
<td>Cache 和 Buffer</td>
<td>查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key 缓存， 权限缓存等</td>
</tr>
</tbody></table>
<h3 id="引擎层"><a href="#引擎层" class="headerlink" title="引擎层"></a>引擎层</h3><p>存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同</p>
<table>
<thead>
<tr>
<th>对比项</th>
<th>MylSAM</th>
<th>InnoDB</th>
</tr>
</thead>
<tbody><tr>
<td>主外键</td>
<td>不支持</td>
<td>支持</td>
</tr>
<tr>
<td>事务</td>
<td>不支持</td>
<td>支持</td>
</tr>
<tr>
<td>行表锁</td>
<td>表锁（不适合高并发）</td>
<td>行锁（适合高并发操作）</td>
</tr>
<tr>
<td>缓存</td>
<td>只缓存索引，不缓存真实数据</td>
<td>不仅缓存索引，还缓存真实数据。对内存要求较高</td>
</tr>
<tr>
<td>表空间</td>
<td>小</td>
<td>大</td>
</tr>
<tr>
<td>关注点</td>
<td>性能</td>
<td>事务</td>
</tr>
<tr>
<td>默认安装</td>
<td>是</td>
<td>是</td>
</tr>
</tbody></table>
<h3 id="存储层"><a href="#存储层" class="headerlink" title="存储层"></a>存储层</h3><p>数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互</p>
<h1 id="二、性能与JOIN"><a href="#二、性能与JOIN" class="headerlink" title="二、性能与JOIN"></a>二、性能与JOIN</h1><h2 id="1、性能下降原因"><a href="#1、性能下降原因" class="headerlink" title="1、性能下降原因"></a>1、性能下降原因</h2><h3 id="索引失效"><a href="#索引失效" class="headerlink" title="索引失效"></a>索引失效</h3><p><strong>单值索引</strong></p>
<p>创建语句</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_表名_字段名 <span class="hljs-keyword">ON</span> 表名(字段名);</code></pre>



<p><strong>复合索引</strong></p>
<pre><code class="hljs angelscript">CREATE INDEX idx_表名_字段名<span class="hljs-number">1</span>字段名<span class="hljs-number">2.</span>.. ON 表名(字段名<span class="hljs-number">1</span>, 字段名<span class="hljs-number">2</span> ...);</code></pre>



<h3 id="关联太多JOIN"><a href="#关联太多JOIN" class="headerlink" title="关联太多JOIN"></a>关联太多JOIN</h3><p>内连接、外连接的表不要过多</p>
<h3 id="服务器调优及参数设置"><a href="#服务器调优及参数设置" class="headerlink" title="服务器调优及参数设置"></a>服务器调优及参数设置</h3><h2 id="2、SQL执行加载顺序"><a href="#2、SQL执行加载顺序" class="headerlink" title="2、SQL执行加载顺序"></a>2、SQL执行加载顺序</h2><h3 id="手写顺序"><a href="#手写顺序" class="headerlink" title="手写顺序"></a>手写顺序</h3><p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814112248.png" srcset="/img/loading.gif" alt=""></p>
<p>随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而<strong>动态调整执行顺序</strong></p>
<p>下面是经常出现的查询顺序：</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814112330.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814112343.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814191644.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="3、7种JOIN"><a href="#3、7种JOIN" class="headerlink" title="3、7种JOIN"></a>3、7种JOIN</h2><p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814150926.png" srcset="/img/loading.gif" alt=""></p>
<h3 id="建表语句"><a href="#建表语句" class="headerlink" title="建表语句"></a><strong>建表语句</strong></h3><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`t_dept`</span> (
<span class="hljs-string">`id`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`deptName`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">30</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <span class="hljs-string">`address`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">40</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)
) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span> AUTO_INCREMENT=<span class="hljs-number">1</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8;
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`t_emp`</span> (
<span class="hljs-string">`id`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`name`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <span class="hljs-string">`age`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <span class="hljs-string">`deptId`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, empno <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>, PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>), <span class="hljs-keyword">KEY</span> <span class="hljs-string">`idx_dept_id`</span> (<span class="hljs-string">`deptId`</span>)
<span class="hljs-comment">#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)</span>
) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span> AUTO_INCREMENT=<span class="hljs-number">1</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8;
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_dept(deptName,address) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'华山'</span>,<span class="hljs-string">'华山'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_dept(deptName,address) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'丐帮'</span>,<span class="hljs-string">'洛阳'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_dept(deptName,address) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'峨眉'</span>,<span class="hljs-string">'峨眉山'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_dept(deptName,address) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'武当'</span>,<span class="hljs-string">'武当山'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_dept(deptName,address) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'明教'</span>,<span class="hljs-string">'光明顶'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_dept(deptName,address) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'少林'</span>,<span class="hljs-string">'少林寺'</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'风清扬'</span>,<span class="hljs-number">90</span>,<span class="hljs-number">1</span>,<span class="hljs-number">100001</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'岳不群'</span>,<span class="hljs-number">50</span>,<span class="hljs-number">1</span>,<span class="hljs-number">100002</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'令狐冲'</span>,<span class="hljs-number">24</span>,<span class="hljs-number">1</span>,<span class="hljs-number">100003</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'洪七公'</span>,<span class="hljs-number">70</span>,<span class="hljs-number">2</span>,<span class="hljs-number">100004</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'乔峰'</span>,<span class="hljs-number">35</span>,<span class="hljs-number">2</span>,<span class="hljs-number">100005</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'灭绝师太'</span>,<span class="hljs-number">70</span>,<span class="hljs-number">3</span>,<span class="hljs-number">100006</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'周芷若'</span>,<span class="hljs-number">20</span>,<span class="hljs-number">3</span>,<span class="hljs-number">100007</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'张三丰'</span>,<span class="hljs-number">100</span>,<span class="hljs-number">4</span>,<span class="hljs-number">100008</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'张无忌'</span>,<span class="hljs-number">25</span>,<span class="hljs-number">5</span>,<span class="hljs-number">100009</span>);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t_emp(<span class="hljs-keyword">NAME</span>,age,deptId,empno) <span class="hljs-keyword">VALUES</span>(<span class="hljs-string">'韦小宝'</span>,<span class="hljs-number">18</span>,<span class="hljs-literal">NULL</span>,<span class="hljs-number">100010</span>);</code></pre>



<h3 id="JOIN查询"><a href="#JOIN查询" class="headerlink" title="JOIN查询"></a>JOIN查询</h3><ul>
<li>笛卡尔积</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_dept, t_emp;</code></pre>

<p>t_dept共20条记录，t_emp共6条记录。两表共同查询后共120条记录</p>
<ul>
<li>内连接</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span>  a.deptId = b.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814153140.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>左外连接</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span>  a.deptId = b.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814153254.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>右外连接</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span>  a.deptId = b.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814153413.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>左外连接<strong>取左表的独有部分</strong></li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span>  a.deptId = b.id <span class="hljs-keyword">WHERE</span> a.deptId <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814153909.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>右外连接<strong>取右表的独有部分</strong></li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id <span class="hljs-keyword">WHERE</span> a.deptId <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814153844.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>注意</strong>：判断字段是否为NULL时，<strong>不能使用’=’</strong></p>
<p>因为</p>
<pre><code class="hljs asciidoc"><span class="hljs-section">= NULL</span></code></pre>

<p>的结果不会报错，但是<strong>结果永远为false</strong>。所以必须使用</p>
<pre><code class="hljs pgsql"><span class="hljs-keyword">IS</span> <span class="hljs-keyword">NULL</span></code></pre>

<p>来进行判空</p>
<ul>
<li>全外连接</li>
</ul>
<p>MySQL不支持全外连接，要查询两个表的全集，需要合并两个查询结果，所以要使用 <strong>UNION</strong> 关键字</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id
<span class="hljs-keyword">UNION</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814154554.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>查询两表独有内容</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id <span class="hljs-keyword">WHERE</span> b.id <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>
<span class="hljs-keyword">UNION</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span> t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id <span class="hljs-keyword">WHERE</span> a.deptId <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814155138.png" srcset="/img/loading.gif" alt=""></p>
<h1 id="三、索引优化"><a href="#三、索引优化" class="headerlink" title="三、索引优化"></a>三、索引优化</h1><h2 id="1、什么是索引"><a href="#1、什么是索引" class="headerlink" title="1、什么是索引"></a>1、什么是索引</h2><ul>
<li><p>MySQL 官方对索引的定义为：<strong>索引（Index）是帮助 MySQL 高效获取数据的数据结构</strong>。可以得到索引的本质： <strong>索引是数据结构</strong>。</p>
<p>可以简单理解为：<strong>排好序的快速查找数据结构</strong></p>
</li>
</ul>
<ul>
<li>在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200814173647.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li>左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用 二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录 </li>
<li>一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上</li>
</ul>
<h2 id="2、索引的优缺点"><a href="#2、索引的优缺点" class="headerlink" title="2、索引的优缺点"></a>2、索引的优缺点</h2><h3 id="优点"><a href="#优点" class="headerlink" title="优点"></a>优点</h3><ul>
<li><strong>提高数据检索的效率</strong>，降低数据库的IO成本</li>
<li>通过索引列对数据进行排序，<strong>降低数据排序的成本</strong>，降低了CPU的消耗</li>
</ul>
<h3 id="缺点"><a href="#缺点" class="headerlink" title="缺点"></a>缺点</h3><ul>
<li>虽然索引大大提高了查询速度，同时却<strong>会降低更新表的速度</strong>，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息</li>
<li>实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以<strong>索引列也是要占用空间的</strong></li>
</ul>
<h2 id="3、索引的分类"><a href="#3、索引的分类" class="headerlink" title="3、索引的分类"></a>3、索引的分类</h2><h3 id="基本语法"><a href="#基本语法" class="headerlink" title="基本语法"></a>基本语法</h3><ul>
<li><p>创建</p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> [<span class="hljs-keyword">UNIQUE</span>] <span class="hljs-keyword">INDEX</span> [indexName] <span class="hljs-keyword">ON</span> table_name(<span class="hljs-keyword">column</span>);</code></pre>
</li>
<li><p>删除</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">INDEX</span> [indexName] <span class="hljs-keyword">ON</span> table_name;</code></pre>
</li>
<li><p>查看</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">INDEX</span> <span class="hljs-keyword">FROM</span> table_name;</code></pre>



</li>
</ul>
<h3 id="分类"><a href="#分类" class="headerlink" title="分类"></a>分类</h3><ul>
<li><p>单值索引</p>
<ul>
<li><p>定义：即一个索引只包含单个列，一个表可以有多个单列索引</p>
</li>
<li><p>语法：</p>
<pre><code class="hljs sql"><span class="hljs-comment">--和表一起创建</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer (
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> AUTO_INCREMENT,
customer_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>),
customer_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>), 
PRIMARY <span class="hljs-keyword">KEY</span>(<span class="hljs-keyword">id</span>), 
<span class="hljs-keyword">KEY</span> (customer_name) <span class="hljs-comment">--单值索引</span>
);

<span class="hljs-comment">--单独创建单值索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_customer_name <span class="hljs-keyword">ON</span> customer(customer_name);</code></pre>
</li>
</ul>
</li>
<li><p>唯一索引</p>
<ul>
<li><p>定义：索引列的值必须唯一，但允许有空值</p>
</li>
<li><p>语法：</p>
<pre><code class="hljs sql"><span class="hljs-comment">--和表一起创建</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer (
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> AUTO_INCREMENT,
customer_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>),
customer_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>), 
PRIMARY <span class="hljs-keyword">KEY</span>(<span class="hljs-keyword">id</span>), 
<span class="hljs-keyword">KEY</span> (customer_name), <span class="hljs-comment">--单值索引</span>
<span class="hljs-keyword">UNIQUE</span> (customer_no) <span class="hljs-comment">--唯一索引</span>
);

<span class="hljs-comment">--单独创建唯一索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">UNIQUE</span> <span class="hljs-keyword">INDEX</span> idx_customer_no <span class="hljs-keyword">ON</span> customer(customer_no);</code></pre>
</li>
</ul>
</li>
<li><p>主键索引</p>
<ul>
<li><p>定义：设定为主键后数据库会<strong>自动建立索引</strong>，innodb为聚簇索引</p>
</li>
<li><p>语法：</p>
<pre><code class="hljs sql"><span class="hljs-comment">--和表一起创建</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer (
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> AUTO_INCREMENT,
customer_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>),
customer_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>), 
PRIMARY <span class="hljs-keyword">KEY</span>(<span class="hljs-keyword">id</span>) <span class="hljs-comment">--主键索引</span>
);

<span class="hljs-comment">--单独创建主键索引</span>
<span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> customer <span class="hljs-keyword">ADD</span> PRIMARY <span class="hljs-keyword">KEY</span> customer(customer_no);

<span class="hljs-comment">--删除主键索引</span>
<span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> customer <span class="hljs-keyword">DROP</span> PRIMARY <span class="hljs-keyword">KEY</span>;

<span class="hljs-comment">--修改建主键索引</span>
必须先删除掉(<span class="hljs-keyword">drop</span>)原索引，再新建(<span class="hljs-keyword">add</span>)索引</code></pre>
</li>
</ul>
</li>
<li><p>复合索引</p>
<ul>
<li><p>定义：即一个索引包含多个列</p>
</li>
<li><p>语法：</p>
<pre><code class="hljs sql"><span class="hljs-comment">--和表一起创建</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> customer (
<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> AUTO_INCREMENT,
customer_no <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>),
customer_name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>), 
PRIMARY <span class="hljs-keyword">KEY</span>(<span class="hljs-keyword">id</span>), 
<span class="hljs-keyword">KEY</span> (customer_name), <span class="hljs-comment">--单值索引</span>
<span class="hljs-keyword">UNIQUE</span> (customer_no), <span class="hljs-comment">--唯一索引</span>
<span class="hljs-keyword">KEY</span> (customer_no,customer_name) <span class="hljs-comment">--复合索引</span>
);

<span class="hljs-comment">--单独创建复合索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_no_name <span class="hljs-keyword">ON</span> customer(customer_no,customer_name);</code></pre>



</li>
</ul>
</li>
</ul>
<h2 id="4、MySQL的索引"><a href="#4、MySQL的索引" class="headerlink" title="4、MySQL的索引"></a>4、MySQL的索引</h2><h3 id="B树与B-树"><a href="#B树与B-树" class="headerlink" title="B树与B+树"></a>B树与B+树</h3><p>树的内容参照<a href="https://nyimac.gitee.io/2020/06/17/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E4%B8%8E%E7%AE%97%E6%B3%95/#B%E6%A0%91%E3%80%81B-%E5%92%8CB-%E6%A0%91">JAVA数据结构 B树、B+树和B*树</a></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200815153029.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200815153043.png" srcset="/img/loading.gif" alt=""></p>
<h4 id="区别"><a href="#区别" class="headerlink" title="区别"></a>区别</h4><ul>
<li>B树的<strong>关键字和记录是放在一起的</strong>，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，<strong>记录只放在叶子节点中</strong></li>
<li>在 B树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而 B+树中每个记录 的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看 B树的性能好像要比 B+树好，而在实际应用中却是 B+树的性能要好些。因为 B+树的非叶子节点不存放实际的数据， 这样每个节点可容纳的元素个数比 B树多，树高比 B树小，这样带来的好处是减少磁盘访问次数。尽管 B+树找到 一个记录所需的比较次数要比 B树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中 B+树的性能可能还会好些，而且 B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有 文件，一个表中的所有记录等），这也是很多数据库和文件系统使用 B+树的缘故</li>
</ul>
<p><strong>为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？</strong></p>
<ul>
<li>B+树的磁盘读写代价更低<ul>
<li>B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对 B 树更小。如果把所有同一内部结点 的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说 IO 读写次数也就降低了</li>
</ul>
</li>
<li>B+树的查询效率更加稳定<ul>
<li>由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当</li>
</ul>
</li>
</ul>
<h3 id="MySQL中的B-树"><a href="#MySQL中的B-树" class="headerlink" title="MySQL中的B+树"></a>MySQL中的B+树</h3><h4 id="主键索引"><a href="#主键索引" class="headerlink" title="主键索引"></a>主键索引</h4><p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822173605.png" srcset="/img/loading.gif" alt=""></p>
<p>MySQL在创建表时，会根据主键来创建主键索引（如果没有主键，会用一个隐藏值来作为主键）。主键索引所构建的B+树，表中所有的记录都存放在了树的最后一层。<strong>且与一般的B+树不同的是：叶子节点间的指针是双向的</strong></p>
<h4 id="复合索引"><a href="#复合索引" class="headerlink" title="复合索引"></a>复合索引</h4><p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822185520.png" srcset="/img/loading.gif" alt=""></p>
<p>创建复合索引时，会将作为<strong>复合索引字段的值</strong>进行排序并放在B+树的最后一层中，同时还会将其<strong>对应的主键值</strong>放在其后。如：</p>
<table>
<thead>
<tr>
<th>a（主键）</th>
<th>b</th>
<th>c</th>
<th>d</th>
<th>e</th>
</tr>
</thead>
<tbody><tr>
<td>2</td>
<td>1</td>
<td>1</td>
<td>1</td>
<td>a</td>
</tr>
</tbody></table>
<p>其中字段a为主键，字段bcd共同作为复合索引，此时存放在最后一层的数据就是：111（复合索引） 2（主键索引）</p>
<p>根据这个特点，可以看出复合索引具有以下使用方法</p>
<ul>
<li><p>最佳左前缀：使用复合索引的顺序必须和创建的<strong>顺序一致</strong></p>
</li>
<li><p>覆盖索引的同时，可以带上主键字段，如</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> a, b, c, d <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p>因为<strong>主键字段和复合索引一起存放在了复合索引说产生的B+树的最后一层</strong>。如果需要a字段，无需进行全表扫描</p>
</li>
<li><p>如果进行范围查找，可能会进行全表扫描，这取决于处在范围内记录的多少</p>
<ul>
<li><p><strong>记录多</strong>，从复合索引映射到主键索引的次数过多，成本过高，<strong>会直接进行全表扫描</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">1</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822175336.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p><strong>记录少</strong>，先<strong>使用复合索引</strong>，然后映射到全表中的对应记录上</p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">80</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822175403.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>但是使用<strong>覆盖索引</strong>，无论记录多少，都会用到索引</p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">1</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822175611.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
</li>
<li><p>不带WHERE也可以通过复合索引查找到主键+复合索引的记录</p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">id</span>, age, <span class="hljs-keyword">name</span>, deptId <span class="hljs-keyword">FROM</span> t_emp ;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822175746.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<h2 id="5、索引的使用场景"><a href="#5、索引的使用场景" class="headerlink" title="5、索引的使用场景"></a>5、索引的使用场景</h2><h3 id="适合索引的场景"><a href="#适合索引的场景" class="headerlink" title="适合索引的场景"></a>适合索引的场景</h3><ul>
<li>主键自动建立唯一索引</li>
<li>频繁作为<strong>查询条件</strong>的字段应该创建索引 </li>
<li>查询中与其它表关联的字段，<strong>外键关系</strong>建立索引 </li>
<li>单键/组合索引的选择问题，<strong>组合索引性价比更高</strong></li>
<li>查询中<strong>排序的字段</strong>，排序字段若通过索引去访问将大大提高排序速度 </li>
<li>查询中<strong>统计</strong>或者<strong>分组</strong>字段</li>
</ul>
<h3 id="不适合索引的场景"><a href="#不适合索引的场景" class="headerlink" title="不适合索引的场景"></a>不适合索引的场景</h3><ul>
<li>表<strong>记录太少</strong>（有无索引差别不大）</li>
<li>经常<strong>增删改</strong>的表或者字段</li>
<li>Where 条件里用不到的字段不创建索引</li>
<li><strong>过滤性不好</strong>的不适合建索引（重复性较高，比如国籍、性别之类的字段）</li>
</ul>
<h1 id="四、Explain-性能分析"><a href="#四、Explain-性能分析" class="headerlink" title="四、Explain 性能分析"></a>四、Explain 性能分析</h1><h2 id="1、概念"><a href="#1、概念" class="headerlink" title="1、概念"></a>1、概念</h2><p>使用 <strong>EXPLAIN</strong> 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。<strong>分析</strong>你的查询语句或是表结构的<strong>性能瓶颈</strong></p>
<h2 id="2、用法"><a href="#2、用法" class="headerlink" title="2、用法"></a>2、用法</h2><pre><code class="hljs sql"><span class="hljs-comment">--EXPLAIN + SQL语句，如：</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> person;</code></pre>



<p><strong>Explain 执行后返回的信息：</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200815171636.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="3、表头字段介绍"><a href="#3、表头字段介绍" class="headerlink" title="3、表头字段介绍"></a>3、表头字段介绍</h2><h3 id="准备工作"><a href="#准备工作" class="headerlink" title="准备工作"></a>准备工作</h3><pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t1(<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<span class="hljs-keyword">content</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-literal">NULL</span> , PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-keyword">id</span>));
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t2(<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<span class="hljs-keyword">content</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-literal">NULL</span> , PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-keyword">id</span>));
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t3(<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<span class="hljs-keyword">content</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-literal">NULL</span> , PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-keyword">id</span>));
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> t4(<span class="hljs-keyword">id</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) AUTO_INCREMENT,<span class="hljs-keyword">content</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-literal">NULL</span> , PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-keyword">id</span>));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t1(<span class="hljs-keyword">content</span>) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">CONCAT</span>(<span class="hljs-string">'t1_'</span>,<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span>+<span class="hljs-keyword">RAND</span>()*<span class="hljs-number">1000</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t2(<span class="hljs-keyword">content</span>) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">CONCAT</span>(<span class="hljs-string">'t2_'</span>,<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span>+<span class="hljs-keyword">RAND</span>()*<span class="hljs-number">1000</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t3(<span class="hljs-keyword">content</span>) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">CONCAT</span>(<span class="hljs-string">'t3_'</span>,<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span>+<span class="hljs-keyword">RAND</span>()*<span class="hljs-number">1000</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> t4(<span class="hljs-keyword">content</span>) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">CONCAT</span>(<span class="hljs-string">'t4_'</span>,<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span>+<span class="hljs-keyword">RAND</span>()*<span class="hljs-number">1000</span>)));</code></pre>



<h3 id="id：表的读取顺序"><a href="#id：表的读取顺序" class="headerlink" title="id：表的读取顺序"></a>id：表的读取顺序</h3><p>id是select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序</p>
<ul>
<li><p><strong>id相同</strong>：执行顺序为 <strong>从上至下执行</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t1, t2, t3 <span class="hljs-keyword">WHERE</span> t1.id = t2.id <span class="hljs-keyword">AND</span> t2.id = t3.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200815173157.png" srcset="/img/loading.gif" alt=""></p>
<p>查询时，表的加载<strong>顺序为t1, t2, t3</strong></p>
</li>
</ul>
<ul>
<li><p><strong>id不同</strong>：执行顺序为 <strong>id大的先执行</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> t2.id <span class="hljs-keyword">FROM</span> t2 <span class="hljs-keyword">WHERE</span> t2.id = 
(<span class="hljs-keyword">SELECT</span> t1.id <span class="hljs-keyword">FROM</span> t1 <span class="hljs-keyword">WHERE</span> t1.id = 
(<span class="hljs-keyword">SELECT</span> t3.id <span class="hljs-keyword">FROM</span> t3)
);</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200815174216.png" srcset="/img/loading.gif" alt=""></p>
<p>查询时，表的加载<strong>顺序为t3, t1, t2</strong> </p>
</li>
</ul>
<ul>
<li><p><strong>id相同又不同</strong>： 执行顺序为 </p>
<ul>
<li>id不同时，值较大的先执行</li>
<li>id相同时，从上至下执行</li>
</ul>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> (<span class="hljs-keyword">SELECT</span> t3.id <span class="hljs-keyword">FROM</span> t3) s1, t2 <span class="hljs-keyword">WHERE</span> s1.id = t2.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200815174740.png" srcset="/img/loading.gif" alt=""></p>
<p>查询时，表的<strong>加载顺序为t3, t2, 虚表dervied2</strong></p>
<ul>
<li>其中dervied<strong>2</strong> 的 2，为 id = 2</li>
</ul>
</li>
</ul>
<h3 id="select-type：查询操作类型"><a href="#select-type：查询操作类型" class="headerlink" title="select_type：查询操作类型"></a>select_type：查询操作类型</h3><p>select_type代表<strong>查询的类型</strong>，主要是用于区别<strong>普通查询、联合查询、子查询等</strong>的复杂查询</p>
<table>
<thead>
<tr>
<th>select_type 属性</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>SIMPLE</td>
<td>简单的 select 查询,查询中<strong>不包含子查询或者 UNION</strong></td>
</tr>
<tr>
<td>PRIMARY</td>
<td>查询中若包含任何复杂的子部分，<strong>最外层</strong>查询则被标记为 Primary</td>
</tr>
<tr>
<td>DERIVED</td>
<td>在 FROM 列表中包含的子查询被标记为 DERIVED(衍生) MySQL 会递归执行这些子查询, 把结果放在临时表里</td>
</tr>
<tr>
<td>SUBQUERY</td>
<td>在SELECT或WHERE列表中包含了<strong>子查询</strong></td>
</tr>
<tr>
<td>DEPEDENT SUBQUERY</td>
<td>在SELECT或WHERE列表中包含了子查询,<strong>子查询基于外层</strong></td>
</tr>
<tr>
<td>UNCACHEABLE SUBQUERY</td>
<td><strong>无法使用缓存</strong>的子查询</td>
</tr>
<tr>
<td>UNION</td>
<td>若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED</td>
</tr>
<tr>
<td>UNION RESULT</td>
<td>从UNION表<strong>获取结果</strong>的SELECT</td>
</tr>
</tbody></table>
<ul>
<li>SUBQUERY  和 DEPEDENT SUBQUERY<ul>
<li>都是 WHERE 后面的条件，SUBQUERY 是单个值（=），DEPEDENT SUBQUERY 是一组值（IN）</li>
</ul>
</li>
</ul>
<ul>
<li>UNCACHEABLE SUBQUERY<ul>
<li>当使用了<strong>@@来引用系统变量</strong>的时候，不会使用缓存</li>
</ul>
</li>
</ul>
<ul>
<li><p>UNION 和  UNION RESULT  </p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span>  t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id <span class="hljs-keyword">WHERE</span> b.id <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>
<span class="hljs-keyword">UNION</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp a <span class="hljs-keyword">RIGHT</span> <span class="hljs-keyword">JOIN</span>  t_dept b <span class="hljs-keyword">ON</span> a.deptId = b.id <span class="hljs-keyword">WHERE</span> a.deptId <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816135453.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<h3 id="table：表的来源"><a href="#table：表的来源" class="headerlink" title="table：表的来源"></a>table：表的来源</h3><p>table表示这个数据是基于哪张表的</p>
<h3 id="type：访问类型"><a href="#type：访问类型" class="headerlink" title="type：访问类型"></a>type：访问类型</h3><p>type 是查询的访问类型。<strong>是较为重要的一个指标</strong>，结果值从最好到最坏依次是：</p>
<pre><code class="hljs pgsql"><span class="hljs-keyword">system</span> &gt; const &gt; eq_ref &gt; <span class="hljs-keyword">ref</span> &gt; fulltext &gt; ref_or_null &gt; index_merge &gt; unique_subquery &gt; index_subquery &gt; range &gt; <span class="hljs-keyword">index</span> &gt; <span class="hljs-keyword">all</span>

<span class="hljs-comment">--常见的顺序为</span>
<span class="hljs-keyword">system</span> &gt; const &gt; eq_ref &gt; <span class="hljs-keyword">ref</span> &gt; range &gt; <span class="hljs-keyword">index</span> &gt; <span class="hljs-keyword">all</span></code></pre>

<p>一般来说，得保证查询<strong>至少达到 range 级别</strong>，最好能达到 ref</p>
<table>
<thead>
<tr>
<th>类型名</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>SYSTEM</td>
<td>表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也<strong>可以忽略不计</strong></td>
</tr>
<tr>
<td>CONST</td>
<td>表示<strong>通过索引一次就找到了</strong>,const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量</td>
</tr>
<tr>
<td>EQ_REF</td>
<td>唯一性索引扫描，对于每个索引键，<strong>表中只有一条记录与之匹配</strong>。常见于主键或唯一索引扫描</td>
</tr>
<tr>
<td>REF</td>
<td>非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它<strong>可能会找到多个符合条件的行</strong>，所以他应该属于查找和扫描的混合体</td>
</tr>
<tr>
<td>RANGE</td>
<td>只检索给定<strong>范围</strong>的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现 了 between、&lt;、&gt;、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而 结束语另一点，不用扫描全部索引</td>
</tr>
<tr>
<td>INDEX</td>
<td>出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组</td>
</tr>
<tr>
<td>ALL</td>
<td>Full Table Scan，将遍历全表以找到匹配的行</td>
</tr>
</tbody></table>
<ul>
<li><p>REF</p>
<pre><code class="hljs sql"><span class="hljs-comment">--其中deptId为索引，且用到了' = '</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> deptId = <span class="hljs-number">3</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816165420.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<ul>
<li><p>RANGE</p>
<pre><code class="hljs sql"><span class="hljs-comment">--其中deptId为索引，用到了 BETWEEN...AND... , IN , &gt; , &lt; 等范围查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> deptId &gt; <span class="hljs-number">3</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816213631.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<ul>
<li><p>INDEX</p>
<pre><code class="hljs sql"><span class="hljs-comment">--其中deptId为索引，查找了整张表时，用到了索引</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> deptId <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816165651.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<ul>
<li><p>ALL</p>
<pre><code class="hljs sql"><span class="hljs-comment">--其中name为非索引</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816165722.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<h3 id="possible-key：可能用到的索引"><a href="#possible-key：可能用到的索引" class="headerlink" title="possible_key：可能用到的索引"></a>possible_key：可能用到的索引</h3><p>显示<strong>可能</strong>应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但<strong>不一 定被查询实际使用</strong></p>
<h3 id="key：实际使用的索引"><a href="#key：实际使用的索引" class="headerlink" title="key：实际使用的索引"></a>key：实际使用的索引</h3><p><strong>实际使用的索引</strong>。如果为NULL，则没有使用索引</p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816172950.png" srcset="/img/loading.gif" alt=""></p>
<p>MySQL推测可能用到主键索引和idx_dept_id索引，实际上用到的是主键索引</p>
<h4 id="覆盖索引"><a href="#覆盖索引" class="headerlink" title="覆盖索引"></a><strong>覆盖索引</strong></h4><p>当查找的字段与建立的索引的匹配（查询的字段都是索引，但不需要全是索引）时，会发生覆盖索引。MySQL推测使用的索引为NULL，而实际上会使用索引</p>
<p>有以下两种解释</p>
<ul>
<li>select的数据列<strong>只用从索引中就能够取得</strong>，不必从数据表中读取，换句话说<strong>查询列要被所使用的索引覆盖</strong></li>
<li>索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引</li>
</ul>
<p>注意：要使用覆盖索引，则<strong>只取出需要的列</strong>（被令为索引），<strong>不要</strong>使用 SELECT *</p>
<pre><code class="hljs sql"><span class="hljs-comment">--其中id和deptId都为索引</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">id</span>, deptId <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816173253.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816173228.png" srcset="/img/loading.gif" alt=""></p>
<h3 id="key-len：索引使用字节数"><a href="#key-len：索引使用字节数" class="headerlink" title="key_len：索引使用字节数"></a>key_len：索引使用字节数</h3><p>表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引</p>
<p><strong>ken_len 越长，说明索引使用的越充分</strong></p>
<h3 id="ref：显示被使用的索引的具体信息"><a href="#ref：显示被使用的索引的具体信息" class="headerlink" title="ref：显示被使用的索引的具体信息"></a>ref：显示被使用的索引的具体信息</h3><p>ref显示索引的哪一列被使用了，如果可能的话，可以是一个常数。哪些列或常量被用于查找索引列上的值</p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_dept, t_emp <span class="hljs-keyword">WHERE</span> t_emp.deptId = t_dept.id;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816194305.png" srcset="/img/loading.gif" alt=""></p>
<h3 id="rows：被查询的行数"><a href="#rows：被查询的行数" class="headerlink" title="rows：被查询的行数"></a>rows：被查询的行数</h3><p>rows 列显示 MySQL 认为它执行查询时必须检查的行数。<strong>越少越好！</strong></p>
<p><strong>验证</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--先删除索引</span>
<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">INDEX</span> idx_dept_id <span class="hljs-keyword">ON</span> t_emp;

<span class="hljs-comment">--查找</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_dept, t_emp <span class="hljs-keyword">WHERE</span> t_emp.deptId = t_dept.id;

<span class="hljs-comment">--再创建索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_dept_id <span class="hljs-keyword">ON</span> t_emp(deptId);

<span class="hljs-comment">--查找</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_dept, t_emp <span class="hljs-keyword">WHERE</span> t_emp.deptId = t_dept.id;</code></pre>



<p><strong>结果如下</strong></p>
<ul>
<li><p>未使用索引时，一共需要查询26行</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816195241.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>使用索引后，一共需要查询6行</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816195401.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<h3 id="Extra：额外重要信息"><a href="#Extra：额外重要信息" class="headerlink" title="Extra：额外重要信息"></a>Extra：额外重要信息</h3><p>其他的额外<strong>重要</strong>的信息</p>
<ul>
<li><p><strong>Using filesort</strong>：使用外部索引排序（未使用用户创建的索引）</p>
<ul>
<li>说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引 完成的排序操作称为“文件排序”</li>
<li>出现 Using filesort <strong>说明SQL语句设计的不好</strong>，<strong>没有按照创建的索引进行排序</strong>，或者<strong>未按照索引指定的顺序进行排序</strong></li>
</ul>
<p><strong>演示</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--创建符合索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_emp_empno_age <span class="hljs-keyword">ON</span> t_emp(empno, age);

<span class="hljs-comment">--进行查询操作，通过 age 字段进行排序（未按照复合索引顺序进行排序查询）</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> empno &gt;<span class="hljs-number">100002</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age;

<span class="hljs-comment">--进行查询操作，通过 empno 或者 empno + age 字段进行排序（按照复合索引顺序进行排序查询）</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> empno &gt;<span class="hljs-number">100002</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> empno;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> empno &gt;<span class="hljs-number">100002</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> empno, age;</code></pre>

<p><strong>结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816205145.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816205226.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816205112.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<ul>
<li><p><strong>Using temporary</strong></p>
<ul>
<li>使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。<strong>常见于排序 order by 和分组查询 group by</strong></li>
<li>出现 Using temporary <strong>说明SQL语句设计的非常不好</strong>，可能是因为没有按照顺序使用复合索引</li>
</ul>
<p><strong>演示</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--进行查询操作， 通过 age 字段进行分组（未按照复合索引顺序进行排序查询）</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_emp_empno_age <span class="hljs-keyword">ON</span> t_emp(empno, age);

<span class="hljs-comment">----进行查询操作，通过 empno 或者 empno + age 字段进行分组（按照复合索引顺序进行排序查询）</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> empno &gt;<span class="hljs-number">100002</span> <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> empno;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> empno &gt;<span class="hljs-number">100002</span> <span class="hljs-keyword">GROUP</span> <span class="hljs-keyword">BY</span> empno, age;</code></pre>

<p><strong>结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816210843.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816210908.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<p>  <strong>重要结论</strong></p>
<p>  如果创建了<strong>复合索引</strong>，一定要<strong>按照复合索引的顺序来使用</strong>，否则会使得性能大幅下降</p>
<ul>
<li><p><strong>Using index</strong></p>
<ul>
<li>Using index 代表表示相应的 select 操作中使用了<strong>覆盖索引</strong>(Covering Index)，详见<a href="https://nyimac.gitee.io/2020/08/16/MySQL%E9%AB%98%E7%BA%A7/#%E8%A6%86%E7%9B%96%E7%B4%A2%E5%BC%95">key：实际用到的索引——覆盖索引</a>，避免访问了表的数据行，<strong>效率不错</strong>！ </li>
<li>如果同时出现 using where，表明<strong>索引被用来执行索引键值的查找</strong></li>
<li>如果没有同时出现 using where，表明<strong>索引只是用来读取数据</strong>而非利用索引执行查找。</li>
</ul>
<p><strong>演示</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--查询 age 字段，使用了WHERE</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age &gt;<span class="hljs-number">100000</span>;

<span class="hljs-comment">--查询 empno 和 age 字段，未使用WHERE</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno, age <span class="hljs-keyword">FROM</span> t_emp;

<span class="hljs-comment">--查询 empno 和 name 字段 （name字段不是索引）</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno, <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><strong>结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816212055.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816212129.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200816212243.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<ul>
<li><p>Using where</p>
<ul>
<li>表明使用了 where 过滤</li>
</ul>
</li>
<li><p>Using join buffer</p>
<ul>
<li>使用了连接缓存</li>
</ul>
</li>
<li><p>impossible where</p>
<ul>
<li>where 子句的值总是 false，不能用来获取任何元组</li>
</ul>
</li>
<li><p>select tables optimized away</p>
<ul>
<li>在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*)操 作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化</li>
</ul>
</li>
</ul>
<h1 id="五、单表查询优化"><a href="#五、单表查询优化" class="headerlink" title="五、单表查询优化"></a>五、单表查询优化</h1><h2 id="1、全值匹配很快捷"><a href="#1、全值匹配很快捷" class="headerlink" title="1、全值匹配很快捷"></a>1、全值匹配很快捷</h2><p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--建立符合索引（age, deptId, name）</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_emp_ade <span class="hljs-keyword">ON</span> t_emp(age, deptId, <span class="hljs-keyword">NAME</span>);

<span class="hljs-comment">--查找</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;

<span class="hljs-comment">--和上一条SQL语句中WHERE后字段的顺序不同，但是不影响查询结果</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span> <span class="hljs-keyword">AND</span> age = <span class="hljs-number">90</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164200.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164226.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164241.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164506.png" srcset="/img/loading.gif" alt=""></p>
<p>可以看到，<strong>复合索引都被用到了，并且SQL中查询字段的顺序，跟使用索引中字段的顺序，没有关系</strong>。优化器会在不影响 SQL 执行结果的前提下，自动地优化</p>
<p><strong>结论：全职匹配我最爱指的是，查询的字段按照顺序在索引中都可以匹配到</strong></p>
<h2 id="2、最佳左前缀法则"><a href="#2、最佳左前缀法则" class="headerlink" title="2、最佳左前缀法则"></a>2、最佳左前缀法则</h2><p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--先删除之前创建的单值索引</span>
<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">INDEX</span> idx_dept_id <span class="hljs-keyword">ON</span> t_emp; 

<span class="hljs-comment">--查询，未按照最佳左前缀法则</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> deptId = <span class="hljs-number">1</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;

<span class="hljs-comment">--查询，部分按照最佳左前缀法则（age字段和复合索引匹配，但name没有）</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span>  age = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;

<span class="hljs-comment">--查询，完全按照最佳左前缀法则</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164932.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164948.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817165100.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164226.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817164506.png" srcset="/img/loading.gif" alt=""></p>
<p>可以看到，查询<strong>字段与索引字段顺序的不同会导致，索引无法充分使用，甚至索引失效</strong></p>
<p><strong>原因</strong>：使用复合索引，需要<strong>遵循最佳左前缀法则</strong>，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的<strong>最左前列开始并且不跳过索引中的列</strong></p>
<p><strong>结论：过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用</strong></p>
<h2 id="3、索引列上不计算"><a href="#3、索引列上不计算" class="headerlink" title="3、索引列上不计算"></a>3、索引列上不计算</h2><p>不在索引列上做任何操作（计算、函数、(自动 or 手动)类型转换），<strong>可能会导致索引失效而转向全表扫描</strong></p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--直接查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">NAME</span> = <span class="hljs-string">'风清扬'</span>;

<span class="hljs-comment">--使用MySQL函数查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">LEFT</span>(age,<span class="hljs-number">2</span>) = <span class="hljs-number">90</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817170139.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817170522.png" srcset="/img/loading.gif" alt=""></p>
<p>可以看出，当age字段使用了left函数以后，<strong>导致索引完全失效</strong></p>
<p><strong>结论：等号左边无计算</strong></p>
<h2 id="4、范围之后全失效"><a href="#4、范围之后全失效" class="headerlink" title="4、范围之后全失效"></a>4、范围之后全失效</h2><p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--范围查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">50</span> <span class="hljs-keyword">AND</span> deptId &gt; <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">NAME</span> = <span class="hljs-string">'风清扬'</span>;

<span class="hljs-comment">--未使用范围查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">50</span> <span class="hljs-keyword">AND</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">name</span> = <span class="hljs-string">'风清扬'</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817171833.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817172159.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817171903.png" srcset="/img/loading.gif" alt=""></p>
<p>可以看出，当对age字段使用范围查询后，使得范围后面的索引失效了</p>
<p><strong>建议：</strong>将可能做范围查询的字段的索引顺序<strong>放在最后</strong></p>
<p><strong>结论：使用范围查询后，如果范围内的记录过多，会导致索引失效</strong>，因为从自定义索引映射到主键索引需要耗费太多的时间，反而不如全表扫描来得快</p>
<h2 id="5、覆盖索引多使用"><a href="#5、覆盖索引多使用" class="headerlink" title="5、覆盖索引多使用"></a>5、覆盖索引多使用</h2><p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--查询所有字段</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_dept <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span>;

<span class="hljs-comment">--查询索引字段</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">id</span> <span class="hljs-keyword">FROM</span> t_dept <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817173338.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817173314.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论：使用覆盖索引（Using index）会提高检索效率</strong></p>
<h2 id="6、使用不等会失效"><a href="#6、使用不等会失效" class="headerlink" title="6、使用不等会失效"></a>6、使用不等会失效</h2><p>在使用<strong>不等于(!= 或者&lt;&gt;)时</strong>，有时会无法使用索引会导致全表扫描</p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--SQL语句中有不等于</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age != <span class="hljs-number">90</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age &lt;&gt; <span class="hljs-number">90</span>;

<span class="hljs-comment">--SQL语句中没有不等于</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817180448.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817180505.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817180521.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论：尽量不要使用不等于</strong></p>
<h2 id="7、使用NULL值要小心"><a href="#7、使用NULL值要小心" class="headerlink" title="7、使用NULL值要小心"></a>7、使用NULL值要小心</h2><p>在使用</p>
<pre><code class="hljs sql">IS NULL
或者
IS NOT NULL</code></pre>

<p>时，可能会导致索引失效</p>
<p>但是如果<strong>允许字段为空</strong>，则</p>
<ul>
<li>IS NULL 不会导致索引失效</li>
<li>IS NOT NULL 会导致索引失效</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817181044.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age <span class="hljs-keyword">IS</span> <span class="hljs-literal">NULL</span>;

<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age <span class="hljs-keyword">IS</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817181116.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817181137.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="8、模糊查询加右边"><a href="#8、模糊查询加右边" class="headerlink" title="8、模糊查询加右边"></a>8、模糊查询加右边</h2><p>要使用模糊查询时，<strong>百分号最好加在右边，而且进行模糊查询的字段必须是单值索引</strong></p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--创建单值索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_emp_name <span class="hljs-keyword">ON</span> t_emp(<span class="hljs-keyword">NAME</span>);

<span class="hljs-comment">--进行模糊查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%风'</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'风%'</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%风%'</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817183416.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817183401.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817183416.png" srcset="/img/loading.gif" alt=""></p>
<p>可以看出，对索引使用模糊查询时，<strong>只有当百分号在右边，索引为单值索引且模糊查询语句在最右边时，索引才会生效</strong></p>
<p>其他情况均失效了</p>
<p><strong>但是</strong>有时必须使用其他类型的模糊查询，这时就需要用<strong>覆盖索引</strong>来解决索引失效的问题</p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%风'</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'风%'</span>;

<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">NAME</span> <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">name</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'%风%'</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817183741.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817183801.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817183741.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论：对索引进行模糊查询时，最好在右边加百分号。必须在左边或左右加百分号时，需要用到覆盖索引来提升查询效率</strong></p>
<h2 id="9、字符串加单引号"><a href="#9、字符串加单引号" class="headerlink" title="9、字符串加单引号"></a>9、字符串加单引号</h2><p>当字段为字符串时，查询时必须带上单引号。否则<strong>会发生自动的类型转换</strong>，从而发生全表扫描</p>
<p><strong>用于查询的表</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817203952.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>其中card_id字段为varchar类型，且设置了单值索引</strong></p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--使用了单引号</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> card_id <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">WHERE</span> card_id = <span class="hljs-string">'1'</span>;

<span class="hljs-comment">--未使用单引号，发生自动类型转换</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> card_id <span class="hljs-keyword">FROM</span> person <span class="hljs-keyword">WHERE</span> card_id = <span class="hljs-number">1</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817204047.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817204027.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="10、尽量不用or查询"><a href="#10、尽量不用or查询" class="headerlink" title="10、尽量不用or查询"></a>10、尽量不用or查询</h2><p>如果使用or，可能导致索引失效。所以要减少or的使用，可以<strong>使用 union all 或者 union 来替代：</strong></p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--使用or进行查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">OR</span> <span class="hljs-keyword">NAME</span> = <span class="hljs-string">'风清扬'</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200817204307.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="口诀"><a href="#口诀" class="headerlink" title="口诀"></a>口诀</h2><p>全职匹配我最爱，最左前缀要遵守</p>
<p>带头大哥不能死，中间兄弟不能断</p>
<p>索引列上少计算，范围之后全失效</p>
<p>LIKE 百分写最右，覆盖索引不写*</p>
<p>不等空值还有 OR，索引影响要注意</p>
<p> VARCHAR 引号不可丢，SQL 优化有诀窍</p>
<h1 id="六、关联查询优化"><a href="#六、关联查询优化" class="headerlink" title="六、关联查询优化"></a>六、关联查询优化</h1><p><strong>建表语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`class`</span> (
<span class="hljs-string">`id`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`card`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>, PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)
);
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">EXISTS</span> <span class="hljs-string">`book`</span> (
<span class="hljs-string">`bookid`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`card`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">10</span>) <span class="hljs-keyword">UNSIGNED</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>, PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`bookid`</span>)
);
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">class</span>(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> book(card) <span class="hljs-keyword">VALUES</span>(<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span> + (<span class="hljs-keyword">RAND</span>() * <span class="hljs-number">20</span>)));</code></pre>



<h2 id="1、LEFT-JOIN优化"><a href="#1、LEFT-JOIN优化" class="headerlink" title="1、LEFT JOIN优化"></a>1、LEFT JOIN优化</h2><p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--未建立索引时的左外连接查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card = book.card;

<span class="hljs-comment">--左表（class）建立索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_class_card <span class="hljs-keyword">ON</span> <span class="hljs-keyword">class</span>(card);

<span class="hljs-comment">--再次执行查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card = book.card;

<span class="hljs-comment">--去掉左表索引</span>
<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">INDEX</span> idx_class_card <span class="hljs-keyword">ON</span> <span class="hljs-keyword">class</span>;

<span class="hljs-comment">--右表建立索引</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_book_card <span class="hljs-keyword">ON</span> book(card);

<span class="hljs-comment">--再次执行查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">LEFT</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card = book.card;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200818170458.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200818170402.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200818170547.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论</strong></p>
<ul>
<li>在优化关联查询时，只有在<strong>被驱动表上建立索引才有效</strong></li>
<li>left join 时，左侧的为驱动表，<strong>右侧为被驱动表</strong></li>
</ul>
<h2 id="2、INNER-JOIN优化"><a href="#2、INNER-JOIN优化" class="headerlink" title="2、INNER JOIN优化"></a>2、INNER JOIN优化</h2><p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--查询操作，目前索引在book表的card上，class表和book表的位置不会改变查询结果</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card = book.card;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> book <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">ON</span> book.card = class.card;

<span class="hljs-comment">--删除book表中的几条记录</span>
<span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> book <span class="hljs-keyword">WHERE</span> bookid&lt;<span class="hljs-number">10</span>;

<span class="hljs-comment">--再次查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card = book.card;

<span class="hljs-comment">--删除book表card字段索引，给class表的card字段添加索引</span>
<span class="hljs-keyword">DROP</span> <span class="hljs-keyword">INDEX</span> idx_book_card <span class="hljs-keyword">ON</span> book;
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">INDEX</span> idx_class_card <span class="hljs-keyword">ON</span> <span class="hljs-keyword">class</span>(card);

<span class="hljs-comment">--再次查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">class</span> <span class="hljs-keyword">INNER</span> <span class="hljs-keyword">JOIN</span> book <span class="hljs-keyword">ON</span> class.card = book.card;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200818171341.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200818171538.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200818171625.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论</strong>：inner join 时，<strong>mysql 会把小结果集的表选为驱动表</strong>（小表驱动大表）</p>
<p><strong>所以最好把索引建立在大表（数据较多的表）上</strong></p>
<h2 id="3、RIGHT-JOIN优化"><a href="#3、RIGHT-JOIN优化" class="headerlink" title="3、RIGHT JOIN优化"></a>3、RIGHT JOIN优化</h2><p>优化类型和LEFT JOIN类似，只不过<strong>被驱动表变成了左表</strong></p>
<h1 id="七、排序分组优化"><a href="#七、排序分组优化" class="headerlink" title="七、排序分组优化"></a>七、排序分组优化</h1><p>在查询中难免会对查询结果进行排序操作。进行排序操作时要<strong>避免出现 Using filesort</strong>，应使用索引给排序带来的方便</p>
<p><strong>索引信息</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819160428.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="1、ORDER-BY-优化"><a href="#1、ORDER-BY-优化" class="headerlink" title="1、ORDER BY 优化"></a>1、ORDER BY 优化</h2><p>以下查询都是在<strong>索引覆盖</strong>的条件下进行的</p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--不满足索引覆盖时进行排序查询</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> empno <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age, deptId;

<span class="hljs-comment">--按照复合索引顺序进行排序</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age, deptId;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age, deptId, <span class="hljs-keyword">name</span>;

<span class="hljs-comment">--不按照复合索引顺序进行排序（无 age 字段），发生Using filesort</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> deptId, <span class="hljs-keyword">name</span>;

<span class="hljs-comment">--不按照复合索引顺序进行排序（索引顺序打乱），发生Using filesort</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> deptId, <span class="hljs-keyword">name</span>, age;

<span class="hljs-comment">--排序时部分(age)升序，部分(deptId)降序，发生Using filesort</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age <span class="hljs-keyword">ASC</span>, deptId <span class="hljs-keyword">DESC</span>;

<span class="hljs-comment">--排序时都为降序</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age &gt; <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> age <span class="hljs-keyword">DESC</span>, deptId <span class="hljs-keyword">DESC</span>;

<span class="hljs-comment">--排序时，在前面的字段为常量时（非范围）</span>
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">50</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> deptId, <span class="hljs-keyword">name</span>;
<span class="hljs-keyword">EXPLAIN</span> <span class="hljs-keyword">SELECT</span> age, deptId <span class="hljs-keyword">FROM</span> t_emp  <span class="hljs-keyword">WHERE</span> age = <span class="hljs-number">50</span> <span class="hljs-keyword">AND</span> deptId&gt;<span class="hljs-number">10000</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> deptId, <span class="hljs-keyword">name</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162506.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162240.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162240.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162240.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162314.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200907210532.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162429.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819162901.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819164020.png" srcset="/img/loading.gif" alt=""></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819164317.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论</strong>：</p>
<p>要想在排序时使用索引，避免 Using filesort，首先需要发生<strong>索引覆盖</strong>，其次</p>
<ul>
<li><p>ORDER BY 后面字段的顺序要和复合索引的<strong>顺序完全一致</strong></p>
</li>
<li><p>ORDER BY 后面的索引必须按照顺序出现，<strong>排在后面的可以不出现</strong></p>
</li>
<li><p>要进行升序或者降序时，<strong>字段的排序顺序必须一致</strong>。不能一部分升序，一部分降序，可以都升序或者都降序</p>
</li>
<li><p>如果复合索引前面的<strong>字段作为常量</strong>出现在过滤条件中，<strong>排序字段可以为紧跟其后的字段</strong></p>
</li>
</ul>
<h3 id="MySQL的排序算法"><a href="#MySQL的排序算法" class="headerlink" title="MySQL的排序算法"></a>MySQL的排序算法</h3><p>当发生 Using filesort 时，MySQL会根据自己的算法对查询结果进行排序</p>
<ul>
<li><strong>双路排序</strong><ul>
<li>MySQL 4.1 之前是使用双路排序,字面意思就是<strong>两次扫描磁盘</strong>，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出</li>
<li>从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段</li>
<li>简单来说，<strong>取一批数据，要对磁盘进行了两次扫描</strong>，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序</li>
</ul>
</li>
<li><strong>单路排序</strong><ul>
<li>从磁盘读取查询需要的所有列，按照 order by 列<strong>在 buffer 对它们进行排序</strong>，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机 IO 变成了顺序 IO,但是它会使用更多的空间， 因为它把每一行都保存在内存中了</li>
<li><strong>存在的问题</strong>：在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能<strong>取出的数据的总大小超出了 sort_buffer 的容量</strong>，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多 路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。也就是<strong>本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失</strong></li>
</ul>
</li>
<li><strong>优化Using filesort</strong><ul>
<li>增大 sort_butter_size 参数的设置<ul>
<li>不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对<strong>每个进程的 1M-8M 之间调整</strong></li>
</ul>
</li>
<li>增大 max_length_for_sort_data 参数的设置<ul>
<li>mysql 使用单路排序的前提是<strong>排序的字段大小要小于 max_length_for_sort_data</strong></li>
<li>提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大， 明显症状是高的磁盘 I/O 活动和低的处理器使用率。（1024-8192 之间调整）</li>
</ul>
</li>
<li>减少 select 后面的查询的字段<ul>
<li>查询的字段减少了，缓冲里就能容纳更多的内容了，<strong>间接增大了sort_buffer_size</strong></li>
</ul>
</li>
</ul>
</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200819164341.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="2、GROUP-BY-优化"><a href="#2、GROUP-BY-优化" class="headerlink" title="2、GROUP BY 优化"></a>2、GROUP BY 优化</h2><p>优化方式和 ORDER BY 类似，参考ORDER BY 的优化方式即可</p>
<h1 id="八、截取查询分析"><a href="#八、截取查询分析" class="headerlink" title="八、截取查询分析"></a>八、截取查询分析</h1><h2 id="1、慢日志查询"><a href="#1、慢日志查询" class="headerlink" title="1、慢日志查询"></a>1、慢日志查询</h2><h3 id="概念"><a href="#概念" class="headerlink" title="概念"></a>概念</h3><ul>
<li>MySQL的慢查询日志是MySQL提供的一种日志记录，<strong>它用来记录在MySQL中响应时间超过阀值的语句</strong>，具体指运行时间超过<strong>long_query_time</strong>值的SQL，则会被记录到慢查询日志中</li>
<li>具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为 10，意思是运行10秒以上的语句</li>
<li>由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能 收集超过5秒的sql，结合之前explain进行全面分析</li>
</ul>
<h3 id="使用"><a href="#使用" class="headerlink" title="使用"></a>使用</h3><p>默认情况下，MySQL 数据库没有开启慢查询日志，需要我们<strong>手动</strong>来设置这个参数</p>
<p>如果不是调优需要的话，<strong>一般不建议启动该参数</strong>，因为开启慢查询日志会或多或少带来一定的性能影响。 慢查询日志支持将日志记录写入文件</p>
<table>
<thead>
<tr>
<th>SQL 语句</th>
<th>描述</th>
<th>备注</th>
</tr>
</thead>
<tbody><tr>
<td>SHOW VARIABLES LIKE ‘%slow_query_log%’</td>
<td>查看慢查询日志是否开启</td>
<td>默认情况下 slow_query_log 的值为 OFF</td>
</tr>
<tr>
<td>set global slow_query_log=1</td>
<td>开启慢查询日志</td>
<td></td>
</tr>
<tr>
<td>SHOW VARIABLES LIKE ‘long_query_time%’</td>
<td>查看慢查询设定阈值</td>
<td>单位：秒</td>
</tr>
<tr>
<td>set long_query_time=1</td>
<td>设定慢查询阈值</td>
<td>单位：秒</td>
</tr>
</tbody></table>
<p>运行查询时间长的 sql，<strong>可以打开慢查询日志查看</strong></p>
<h2 id="2、批量数据脚本"><a href="#2、批量数据脚本" class="headerlink" title="2、批量数据脚本"></a>2、批量数据脚本</h2><h3 id="建表语句-1"><a href="#建表语句-1" class="headerlink" title="建表语句"></a>建表语句</h3><pre><code class="hljs sql"><span class="hljs-comment">--dept 部门表</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`dept`</span> (
<span class="hljs-string">`id`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`deptName`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">30</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <span class="hljs-string">`address`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">40</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, ceo <span class="hljs-built_in">INT</span> <span class="hljs-literal">NULL</span> , PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)
) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span> AUTO_INCREMENT=<span class="hljs-number">1</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8;

<span class="hljs-comment">-- emp 员工表</span>
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`emp`</span> (
<span class="hljs-string">`id`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT, <span class="hljs-string">`empno`</span> <span class="hljs-built_in">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> , <span class="hljs-string">`name`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">20</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <span class="hljs-string">`age`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">3</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, <span class="hljs-string">`deptId`</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>, PRIMARY <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>)
<span class="hljs-comment">#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)</span>
) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">INNODB</span> AUTO_INCREMENT=<span class="hljs-number">1</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8;</code></pre>



<h3 id="设置参数"><a href="#设置参数" class="headerlink" title="设置参数"></a>设置参数</h3><p>在执行创建函数之前，首先请保证 log_bin_trust_function_creators 参数为 1，即 on 开启状态。 否则会报错</p>
<pre><code class="hljs sql"><span class="hljs-comment">--查询</span>
<span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">VARIABLES</span> <span class="hljs-keyword">LIKE</span> <span class="hljs-string">'log_bin_trust_function_creators'</span>;

<span class="hljs-comment">--设置</span>
<span class="hljs-keyword">SET</span> <span class="hljs-keyword">GLOBAL</span> log_bin_trust_function_creators=<span class="hljs-number">1</span>;</code></pre>



<h3 id="编写随机函数"><a href="#编写随机函数" class="headerlink" title="编写随机函数"></a>编写随机函数</h3><h4 id="随机产生字符串"><a href="#随机产生字符串" class="headerlink" title="随机产生字符串"></a><strong>随机产生字符串</strong></h4><pre><code class="hljs sql"><span class="hljs-comment">--DELIMITER 是用于改变结束的标志的，一般以分号结尾，但这里改为了以 $$ 结尾</span>
DELIMITER $$
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span> rand_string(n <span class="hljs-built_in">INT</span>) <span class="hljs-keyword">RETURNS</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">255</span>)
<span class="hljs-keyword">BEGIN</span>
<span class="hljs-keyword">DECLARE</span> chars_str <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">100</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'</span>;
<span class="hljs-keyword">DECLARE</span> return_str <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">255</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>;
<span class="hljs-keyword">DECLARE</span> i <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;
WHILE i &lt; n DO
<span class="hljs-keyword">SET</span> return_str =<span class="hljs-keyword">CONCAT</span>(return_str,<span class="hljs-keyword">SUBSTRING</span>(chars_str,<span class="hljs-keyword">FLOOR</span>(<span class="hljs-number">1</span>+<span class="hljs-keyword">RAND</span>()*<span class="hljs-number">52</span>),<span class="hljs-number">1</span>));
<span class="hljs-keyword">SET</span> i = i + <span class="hljs-number">1</span>;
<span class="hljs-keyword">END</span> <span class="hljs-keyword">WHILE</span>;
RETURN return_str;
<span class="hljs-keyword">END</span> $$</code></pre>

<p>如果要<strong>删除函数</strong>，则执行：</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">FUNCTION</span> rand_string;</code></pre>



<h4 id="随机产生部门编号"><a href="#随机产生部门编号" class="headerlink" title="随机产生部门编号"></a>随机产生部门编号</h4><pre><code class="hljs sql">DELIMITER $$
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">FUNCTION</span> rand_num (from_num <span class="hljs-built_in">INT</span> ,to_num <span class="hljs-built_in">INT</span>) <span class="hljs-keyword">RETURNS</span> <span class="hljs-built_in">INT</span>(<span class="hljs-number">11</span>)
<span class="hljs-keyword">BEGIN</span>
<span class="hljs-keyword">DECLARE</span> i <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;
<span class="hljs-keyword">SET</span> i = <span class="hljs-keyword">FLOOR</span>(from_num +<span class="hljs-keyword">RAND</span>()*(to_num -from_num+<span class="hljs-number">1</span>)) ;
RETURN i;
<span class="hljs-keyword">END</span>$$</code></pre>

<p>如果要<strong>删除函数</strong>，则执行：</p>
<pre><code class="hljs sql"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">function</span> rand_num;</code></pre>



<h3 id="创建存储过程"><a href="#创建存储过程" class="headerlink" title="创建存储过程"></a>创建存储过程</h3><h4 id="创建往-emp-表中插入数据的存储过程"><a href="#创建往-emp-表中插入数据的存储过程" class="headerlink" title="创建往 emp 表中插入数据的存储过程"></a>创建往 emp 表中插入数据的存储过程</h4><pre><code class="hljs sql">DELIMITER $$
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> insert_emp( <span class="hljs-keyword">START</span> <span class="hljs-built_in">INT</span> , max_num <span class="hljs-built_in">INT</span> )
<span class="hljs-keyword">BEGIN</span>
<span class="hljs-keyword">DECLARE</span> i <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;
<span class="hljs-comment">#set autocommit =0 把 autocommit 设置成 0</span>
<span class="hljs-keyword">SET</span> autocommit = <span class="hljs-number">0</span>;
REPEAT
<span class="hljs-keyword">SET</span> i = i + <span class="hljs-number">1</span>;
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> emp (empno, <span class="hljs-keyword">NAME</span> ,age ,deptid ) <span class="hljs-keyword">VALUES</span> ((<span class="hljs-keyword">START</span>+i) ,rand_string(<span class="hljs-number">6</span>) , rand_num(<span class="hljs-number">30</span>,<span class="hljs-number">50</span>),rand_num(<span class="hljs-number">1</span>,<span class="hljs-number">10000</span>));
UNTIL i = max_num
<span class="hljs-keyword">END</span> <span class="hljs-keyword">REPEAT</span>;
<span class="hljs-keyword">COMMIT</span>;
<span class="hljs-keyword">END</span>$$

<span class="hljs-comment">--删除</span>
<span class="hljs-comment">-- DELIMITER ;</span>
<span class="hljs-comment">-- drop PROCEDURE insert_emp;</span></code></pre>



<h4 id="创建往-dept-表中插入数据的存储过程"><a href="#创建往-dept-表中插入数据的存储过程" class="headerlink" title="创建往 dept 表中插入数据的存储过程"></a>创建往 dept 表中插入数据的存储过程</h4><pre><code class="hljs sql"><span class="hljs-comment">--执行存储过程，往 dept 表添加随机数据</span>
DELIMITER $$
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> <span class="hljs-string">`insert_dept`</span>( max_num <span class="hljs-built_in">INT</span> )
<span class="hljs-keyword">BEGIN</span>
<span class="hljs-keyword">DECLARE</span> i <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;
<span class="hljs-keyword">SET</span> autocommit = <span class="hljs-number">0</span>;
REPEAT
<span class="hljs-keyword">SET</span> i = i + <span class="hljs-number">1</span>;
<span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> dept ( deptname,address,ceo ) <span class="hljs-keyword">VALUES</span> (rand_string(<span class="hljs-number">8</span>),rand_string(<span class="hljs-number">10</span>),rand_num(<span class="hljs-number">1</span>,<span class="hljs-number">500000</span>));
UNTIL i = max_num
<span class="hljs-keyword">END</span> <span class="hljs-keyword">REPEAT</span>;
<span class="hljs-keyword">COMMIT</span>;
<span class="hljs-keyword">END</span>$$

<span class="hljs-comment">--删除</span>
<span class="hljs-comment">-- DELIMITER ;</span>
<span class="hljs-comment">-- drop PROCEDURE insert_dept;</span></code></pre>



<h3 id="调用存储过程"><a href="#调用存储过程" class="headerlink" title="调用存储过程"></a>调用存储过程</h3><h4 id="添加数据到部门表"><a href="#添加数据到部门表" class="headerlink" title="添加数据到部门表"></a>添加数据到部门表</h4><pre><code class="hljs sql"><span class="hljs-comment">--执行存储过程，往 dept 表添加 1 万条数据</span>
DELIMITER ;
<span class="hljs-keyword">CALL</span> insert_dept(<span class="hljs-number">10000</span>);</code></pre>



<h4 id="添加数据到员工表"><a href="#添加数据到员工表" class="headerlink" title="添加数据到员工表"></a>添加数据到员工表</h4><pre><code class="hljs sql"><span class="hljs-comment">--执行存储过程，往 emp 表添加 50 万条数据</span>
DELIMITER ;
<span class="hljs-keyword">CALL</span> insert_emp(<span class="hljs-number">100000</span>,<span class="hljs-number">500000</span>);</code></pre>



<h3 id="批量删除某个表上的所有索引"><a href="#批量删除某个表上的所有索引" class="headerlink" title="批量删除某个表上的所有索引"></a>批量删除某个表上的所有索引</h3><h4 id="删除索引的存储过程"><a href="#删除索引的存储过程" class="headerlink" title="删除索引的存储过程"></a>删除索引的存储过程</h4><pre><code class="hljs sql">DELIMITER $$
<span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">PROCEDURE</span> <span class="hljs-string">`proc_drop_index`</span>(dbname <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>),tablename <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>))
<span class="hljs-keyword">BEGIN</span>
<span class="hljs-keyword">DECLARE</span> done <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;
<span class="hljs-keyword">DECLARE</span> ct <span class="hljs-built_in">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>;
<span class="hljs-keyword">DECLARE</span> _index <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">200</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-string">''</span>;
<span class="hljs-keyword">DECLARE</span> _cur <span class="hljs-keyword">CURSOR</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">SELECT</span> index_name <span class="hljs-keyword">FROM</span> information_schema.STATISTICS <span class="hljs-keyword">WHERE</span>
table_schema=dbname <span class="hljs-keyword">AND</span> table_name=tablename <span class="hljs-keyword">AND</span> seq_in_index=<span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> index_name &lt;&gt;<span class="hljs-string">'PRIMARY'</span> ;
<span class="hljs-keyword">DECLARE</span> CONTINUE <span class="hljs-keyword">HANDLER</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">FOUND</span> <span class="hljs-keyword">set</span> done=<span class="hljs-number">2</span> ;
OPEN _cur;
FETCH _cur INTO _index;
WHILE _index&lt;&gt;'' DO
<span class="hljs-keyword">SET</span> @<span class="hljs-keyword">str</span> = <span class="hljs-keyword">CONCAT</span>(<span class="hljs-string">"drop index "</span>,_index,<span class="hljs-string">" on "</span>,tablename );
<span class="hljs-keyword">PREPARE</span> sql_str <span class="hljs-keyword">FROM</span> @<span class="hljs-keyword">str</span> ;
<span class="hljs-keyword">EXECUTE</span> sql_str;
<span class="hljs-keyword">DEALLOCATE</span> <span class="hljs-keyword">PREPARE</span> sql_str;
<span class="hljs-keyword">SET</span> _index=<span class="hljs-string">''</span>;
FETCH _cur INTO _index;
<span class="hljs-keyword">END</span> <span class="hljs-keyword">WHILE</span>;
CLOSE _cur;
<span class="hljs-keyword">END</span>$$</code></pre>



<h4 id="执行存储过程"><a href="#执行存储过程" class="headerlink" title="执行存储过程"></a>执行存储过程</h4><pre><code class="hljs sql"><span class="hljs-keyword">CALL</span> proc_drop_index(<span class="hljs-string">"dbname"</span>,<span class="hljs-string">"tablename"</span>);</code></pre>



<h1 id="九、MySQL锁机制"><a href="#九、MySQL锁机制" class="headerlink" title="九、MySQL锁机制"></a>九、MySQL锁机制</h1><h2 id="1、表锁"><a href="#1、表锁" class="headerlink" title="1、表锁"></a>1、表锁</h2><p><strong>MylSAM引擎使用表锁，并且不支持事务</strong></p>
<p><strong>SQL语句</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--展示表是否加锁</span>
<span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">OPEN</span> <span class="hljs-keyword">TABLES</span>;

<span class="hljs-comment">--加锁 read (读锁) write (写锁)</span>
<span class="hljs-keyword">LOCK</span> <span class="hljs-keyword">TABLE</span> table1 <span class="hljs-keyword">read</span>(write), table2 <span class="hljs-keyword">read</span>(write)...

<span class="hljs-comment">--全部解锁</span>
<span class="hljs-keyword">UNLOCK</span> <span class="hljs-keyword">TABLES</span>;</code></pre>



<h3 id="读锁"><a href="#读锁" class="headerlink" title="读锁"></a>读锁</h3><ul>
<li><p>主机A给表加上<strong>表锁（读锁）</strong>以后</p>
<ul>
<li>主机A和其他主机都可以读取<strong>该表</strong>的信息</li>
<li><strong>主机A不能读取库中其他表的信息</strong>，但其他主机可以读取库中所有表的信息</li>
<li>如果要修改被锁表的信息<ul>
<li>主机A如果对表进行修改，<strong>会修改失败</strong></li>
<li>其他主机对表进行修改，<strong>会被阻塞，直到锁被释放</strong></li>
</ul>
</li>
</ul>
</li>
</ul>
<p><strong>演示</strong></p>
<ul>
<li><p>给dept表加锁并查询状态</p>
<pre><code class="hljs sql"><span class="hljs-keyword">LOCK</span> <span class="hljs-keyword">TABLE</span> dept <span class="hljs-keyword">READ</span>;

<span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">OPEN</span> <span class="hljs-keyword">TABLES</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820151441.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<p><strong>读取</strong></p>
<ul>
<li><p>两个客户端分别读取dept表的信息，都能读出来</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">id</span> <span class="hljs-keyword">FROM</span> dept <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span>;</code></pre>
</li>
<li><p>客户端A（加锁端）A<strong>读取其他表</strong>信息，<strong>读取失败</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820152614.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>其他客户端读取度其他表信息，读取成功</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820152714.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<p><strong>修改</strong></p>
<ul>
<li><p>客户端A对表中内容进行修改，<strong>修改失败</strong></p>
<pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> dept <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820151654.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>客户端B对表中内容进行修改，进入阻塞状态</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> dept <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span>;</code></pre>



</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820151737.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li><p>从客户端A解锁后，客户端B修改成功</p>
<pre><code class="hljs sql"><span class="hljs-keyword">UNLOCK</span> <span class="hljs-keyword">TABLES</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820151818.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<h3 id="写锁"><a href="#写锁" class="headerlink" title="写锁"></a>写锁</h3><p>主机A给表加上<strong>表锁（写锁）</strong>以后</p>
<ul>
<li>主机A可以读取该表信息，但<strong>其他主机读取时，会进入阻塞状态，知道读锁被释放</strong></li>
<li><strong>主机A不能读取库中其他表的信息</strong>，但其他主机可以读取库中<strong>除该表以外所有表</strong>的信息</li>
<li>如果要修改被锁表的信息<ul>
<li>主机A如果对表进行修改，修改成功</li>
<li>其他主机对表进行修改，<strong>会被阻塞，直到锁被释放</strong></li>
</ul>
</li>
</ul>
<p><strong>演示</strong></p>
<ul>
<li><p>给dept表加上写锁并查看</p>
<pre><code class="hljs sql"><span class="hljs-keyword">LOCK</span> <span class="hljs-keyword">TABLE</span> dept WRITE;

<span class="hljs-keyword">SHOW</span> <span class="hljs-keyword">OPEN</span> <span class="hljs-keyword">TABLES</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820153259.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<p><strong>读取</strong></p>
<ul>
<li><p>客户端A查询该表内容，查询成功；读取其他表，读取失败</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> dept;

<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820153403.png" srcset="/img/loading.gif" alt=""></p>
</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820153437.png" srcset="/img/loading.gif" alt=""></p>
<ul>
<li><p>其他表读取该表信息，进入阻塞状态</p>
<pre><code class="hljs sql"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> dept;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820153517.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>释放后，读取成功</p>
<pre><code class="hljs SQL"><span class="hljs-keyword">UNLOCK</span> <span class="hljs-keyword">TABLES</span>;</code></pre>



</li>
</ul>
<p><strong>修改</strong></p>
<ul>
<li><p>客户端A修改<strong>该表</strong>内容，修改成功</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> dept <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820153637.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>客户端A修改<strong>其他表</strong>内容，修改失败</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820153710.png" srcset="/img/loading.gif" alt=""></p>
</li>
<li><p>其他客户端修改<strong>该表</strong>内容，进入阻塞状态</p>
<pre><code class="hljs sql"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;</code></pre>



</li>
</ul>
<h3 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h3><p><strong>读锁不会阻塞读，只会阻塞写。但是写锁会阻塞读和写。</strong></p>
<h2 id="2、行锁"><a href="#2、行锁" class="headerlink" title="2、行锁"></a>2、行锁</h2><p><strong>InnoDB使用行锁，并且支持事务</strong>，事务相关可参考 <a href="https://nyimac.gitee.io/2020/08/11/MySQL%E5%9F%BA%E7%A1%80/#%E5%85%AD%E3%80%81%E4%BA%8B%E5%8A%A1"><strong>MySQL基础</strong></a></p>
<h3 id="特点"><a href="#特点" class="headerlink" title="特点"></a>特点</h3><p>如果两个客户端<strong>对同一条记录进行修改</strong></p>
<ul>
<li>客户端A修改后，未提交（未commit），此时客户端B修改，则会阻塞</li>
<li>客户端A修改后，提交后，客户端B再修改，则不会阻塞</li>
</ul>
<p>如果两个客户端分别<strong>对不同的记录进行修改</strong>，则不会被阻塞</p>
<p><strong>修改同一条记录</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--关闭自动提交</span>
<span class="hljs-keyword">SET</span> autocommit = <span class="hljs-number">0</span>;

<span class="hljs-comment">--客户端A、B查询id=2的记录</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;

<span class="hljs-comment">--客户端A进行修改操作（将年龄改为了80），但未提交</span>
<span class="hljs-keyword">UPDATE</span> t_emp <span class="hljs-keyword">SET</span> age = <span class="hljs-number">80</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;

<span class="hljs-comment">--客户端A进行查询</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;

<span class="hljs-comment">--客户端B进行查询</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;

<span class="hljs-comment">--客户端B进行修改（客户端A未提交）</span>
<span class="hljs-keyword">UPDATE</span> t_emp <span class="hljs-keyword">SET</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;

<span class="hljs-comment">--客户端A提交</span>
<span class="hljs-keyword">COMMIT</span>;

<span class="hljs-comment">--客户端B提交</span>
<span class="hljs-keyword">COMMIT</span>;</code></pre>

<p><strong>对应结果</strong></p>
<p>客户端A查询结果</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820163718.png" srcset="/img/loading.gif" alt=""></p>
<p>客户端B查询结果</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820163718.png" srcset="/img/loading.gif" alt=""></p>
<p>客户端A修改后A查询</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820163847.png" srcset="/img/loading.gif" alt=""></p>
<p>客户端A修改后B查询</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820163718.png" srcset="/img/loading.gif" alt=""></p>
<p>客户端A修改，未提交，此时B进行修改，<strong>被阻塞</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820163957.png" srcset="/img/loading.gif" alt=""></p>
<p>客户端A提交后，B修改成功</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820164036.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>修改不同记录</strong></p>
<pre><code class="hljs sql"><span class="hljs-comment">--客户端A对id=2的年龄进行修改</span>
<span class="hljs-keyword">UPDATE</span> t_emp <span class="hljs-keyword">SET</span> age = <span class="hljs-number">90</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">2</span>;

<span class="hljs-comment">--客户端B对id=3的年龄进行修改</span>
<span class="hljs-keyword">UPDATE</span> t_emp <span class="hljs-keyword">SET</span> age = <span class="hljs-number">30</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">3</span>;

<span class="hljs-comment">--客户端A，B分别提交</span>
<span class="hljs-keyword">COMMIT</span>;
<span class="hljs-keyword">COMMIT</span>;</code></pre>

<p>因为InnoDB使用行锁，对于不同行的操作，不会出现阻塞现象</p>
<h3 id="索引失效-1"><a href="#索引失效-1" class="headerlink" title="索引失效"></a>索引失效</h3><p>索引失效，<strong>行锁变表锁</strong></p>
<p>当<strong>索引失效</strong>后，即使多个客户端操作的不是同一条记录，<strong>如果未提交，其他客户端也会进入阻塞状态</strong></p>
<p>所以要<strong>避免索引失效</strong></p>
<h3 id="间隙锁危害"><a href="#间隙锁危害" class="headerlink" title="间隙锁危害"></a>间隙锁危害</h3><h4 id="概念-1"><a href="#概念-1" class="headerlink" title="概念"></a>概念</h4><p>当我们用<strong>范围条件</strong>而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁</p>
<p>对于键值<strong>在条件范围内但并不存在的记录</strong>，叫做<strong>“间隙(GAP)</strong>” ，<strong>InnoDB也会对这个“间隙”加锁</strong>，这种锁机制就是所谓的间隙锁(Next-Key锁)。</p>
<h4 id="危害"><a href="#危害" class="headerlink" title="危害"></a>危害</h4><p>因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。<br>间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无<br>法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害</p>
<h4 id="演示"><a href="#演示" class="headerlink" title="演示"></a>演示</h4><pre><code class="hljs sql"><span class="hljs-comment">--查询表记录，此处没有id=2的记录</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp;

<span class="hljs-comment">--客户端A进行范围查询，但是范围内没有id=2的记录</span>
<span class="hljs-keyword">UPDATE</span> t_emp <span class="hljs-keyword">SET</span> deptId = <span class="hljs-number">1</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span>&gt;<span class="hljs-number">1</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">id</span> &lt; <span class="hljs-number">6</span>;

<span class="hljs-comment">--客户端B进行插入数据，插入一条id=2的记录</span>
<span class="hljs-keyword">INSERT</span> t_emp <span class="hljs-keyword">VALUES</span>(<span class="hljs-number">2</span>, <span class="hljs-string">'岳不群'</span>, <span class="hljs-number">11</span>, <span class="hljs-number">2</span>, <span class="hljs-number">100002</span>); 

<span class="hljs-comment">--客户端A提交</span>
<span class="hljs-keyword">COMMIT</span>;

<span class="hljs-comment">--客户端B提交</span>
<span class="hljs-keyword">COMMIT</span>;</code></pre>

<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820170126.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>客户端B进入阻塞状态</strong></p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820170617.png" srcset="/img/loading.gif" alt=""></p>
<p>提交后，插入成功</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200820170654.png" srcset="/img/loading.gif" alt=""></p>
<p><strong>结论</strong>：可以看到表中本来<strong>没有id=2的记录</strong>，但是在客户端A进行<strong>范围修改</strong>时，客户端B对<strong>在范围内但不存在的数据进行插入时，客户端B进入了阻塞状态</strong></p>
<h3 id="锁住指定的一行"><a href="#锁住指定的一行" class="headerlink" title="锁住指定的一行"></a>锁住指定的一行</h3><pre><code class="hljs sql"><span class="hljs-keyword">BEGIN</span>;

<span class="hljs-comment">--锁住指定的一行，如果进行更新操作就是 ... FOR UPDATE，删除操作就是 ... FOR DELETE 以此类推</span>
<span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> t_emp <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span> <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">UPDATE</span>;

<span class="hljs-comment">--进行修改操作</span>
<span class="hljs-keyword">UPDATE</span> t_emp <span class="hljs-keyword">SET</span> <span class="hljs-keyword">NAME</span> = <span class="hljs-string">'风车车'</span> <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">id</span> = <span class="hljs-number">1</span>;

<span class="hljs-comment">--提交</span>
<span class="hljs-keyword">COMMIT</span>;</code></pre>

<p>如果当某一行被锁住后，其他客户端对改行进行操作，会被<strong>阻塞</strong></p>
<h3 id="总结-1"><a href="#总结-1" class="headerlink" title="总结"></a>总结</h3><p>Innodb存储引擎由于实现了<strong>行级锁定</strong>，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些， 但是在整体<strong>并</strong><br><strong>发处理能力方面要远远优于MyISAM的表级锁定的</strong>。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的<br>优势了。<br>但是，Innodb的行级锁定同样也有其脆弱的一面，当我们<strong>使用不当的时候</strong>，可能会让Innodb的整体性能表现不仅不能比MylSAM高<br>，甚至可能会更差。</p>
<h1 id="十、复制"><a href="#十、复制" class="headerlink" title="十、复制"></a>十、复制</h1><h2 id="1、主从复制"><a href="#1、主从复制" class="headerlink" title="1、主从复制"></a>1、主从复制</h2><p>主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。</p>
<ul>
<li><strong>binlog 线程</strong> ：负责将主服务器上的数据更改<strong>写入二进制日志</strong>（Binary log）中。</li>
<li><strong>I/O 线程</strong> ：负责从主服务器上读取二进制日志，并<strong>写入从服务器的中继日志</strong>（Relay log）。</li>
<li><strong>SQL 线程</strong> ：负责<strong>读取中继日志</strong>，解析出主服务器已经执行的数据更改并在从服务器中重放（Replay）。</li>
</ul>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822133613.png" srcset="/img/loading.gif" alt=""></p>
<h2 id="2、读写分离"><a href="#2、读写分离" class="headerlink" title="2、读写分离"></a>2、读写分离</h2><p><strong>主服务器处理写操作</strong>以及实时性要求比较高的读操作，而<strong>从服务器处理读操作</strong></p>
<p>读写分离能提高性能的原因在于：</p>
<ul>
<li>主从服务器负责各自的读和写，极大程度<strong>缓解了锁的争用</strong></li>
<li><strong>从服务器</strong>可以使用 <a href="https://nyimac.gitee.io/2020/08/16/MySQL%E9%AB%98%E7%BA%A7/#%E5%BC%95%E6%93%8E%E5%B1%82">MyISAM</a>，提升查询性能以及节约系统开销</li>
<li>增加冗余，提高可用性</li>
</ul>
<p>读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器</p>
<p><img src="https://nyimapicture.oss-cn-beijing.aliyuncs.com/img/20200822133739.png" srcset="/img/loading.gif" alt=""></p>

            </article>
            <hr>
            <div>
              <div class="post-metas mb-3">
                
                  <div class="post-meta mr-3">
                    <i class="iconfont icon-category"></i>
                    
                      <a class="hover-with-bg" href="/categories/MySQL/">MySQL</a>
                    
                  </div>
                
                
              </div>
              
                <p class="note note-warning">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-sa/4.0/deed.zh" target="_blank" rel="nofollow noopener noopener">CC BY-SA 4.0 协议</a> ，转载请注明出处！</p>
              
              
                <div class="post-prevnext row">
                  <div class="post-prev col-6">
                    
                    
                      <a href="/2020/09/12/%E6%8C%87%E4%BB%A4%E6%B5%81%E7%A8%8B%E5%92%8C%E4%B8%AD%E6%96%AD/">
                        <i class="iconfont icon-arrowleft"></i>
                        <span class="hidden-mobile">指令流程和中断</span>
                        <span class="visible-mobile">Previous</span>
                      </a>
                    
                  </div>
                  <div class="post-next col-6">
                    
                    
                      <a href="/2020/08/11/MySQL%E5%9F%BA%E7%A1%80/">
                        <span class="hidden-mobile">MySQL基础</span>
                        <span class="visible-mobile">Next</span>
                        <i class="iconfont icon-arrowright"></i>
                      </a>
                    
                  </div>
                </div>
              
            </div>

            
              <!-- Comments -->
              <div class="comments" id="comments">
                
                
  <div class="disqus" style="width:100%">
    <div id="disqus_thread"></div>
    <script type="text/javascript">
      function loadDisqus() {
        var disqus_config = function () {
          this.page.url = 'http://nyimac.gitee.io/2020/08/16/MySQL高级/';
          this.page.identifier = '/2020/08/16/MySQL高级/';
        };
        (function () {
          var d = document,
            s = d.createElement('script');
          s.src = '//' + '' + '.disqus.com/embed.js';
          s.setAttribute('data-timestamp', new Date());
          (d.head || d.body).appendChild(s);
        })();
      }
      createObserver(loadDisqus, 'disqus_thread');
    </script>
    <noscript>Please enable JavaScript to view the
      <a href="https://disqus.com/?ref_noscript" target="_blank" rel="nofollow noopener noopener">comments powered by Disqus.</a>
    </noscript>
  </div>


              </div>
            
          </div>
        </div>
      </div>
    </div>
    
      <div class="d-none d-lg-block col-lg-2 toc-container" id="toc-ctn">
        <div id="toc">
  <p class="toc-header"><i class="iconfont icon-list"></i>&nbsp;TOC</p>
  <div id="tocbot"></div>
</div>

      </div>
    
  </div>
</div>

<!-- Custom -->


    
  </main>

  
    <a id="scroll-top-button" href="#" role="button">
      <i class="iconfont icon-arrowup" aria-hidden="true"></i>
    </a>
  

  
    <div class="modal fade" id="modalSearch" tabindex="-1" role="dialog" aria-labelledby="ModalLabel"
     aria-hidden="true">
  <div class="modal-dialog modal-dialog-scrollable modal-lg" role="document">
    <div class="modal-content">
      <div class="modal-header text-center">
        <h4 class="modal-title w-100 font-weight-bold">Search</h4>
        <button type="button" id="local-search-close" class="close" data-dismiss="modal" aria-label="Close">
          <span aria-hidden="true">&times;</span>
        </button>
      </div>
      <div class="modal-body mx-3">
        <div class="md-form mb-5">
          <input type="text" id="local-search-input" class="form-control validate">
          <label data-error="x" data-success="v"
                 for="local-search-input">keyword</label>
        </div>
        <div class="list-group" id="local-search-result"></div>
      </div>
    </div>
  </div>
</div>
  

  

  

  <footer class="mt-5">
  <div class="text-center py-3">
    <div>
      <a href="https://hexo.io" target="_blank" rel="nofollow noopener"><span>Hexo</span></a>
      <i class="iconfont icon-love"></i>
      <a href="https://github.com/fluid-dev/hexo-theme-fluid" target="_blank" rel="nofollow noopener">
        <span>Fluid</span></a>
    </div>
    
  <div class="statistics">
    
    

    
      
        <!-- 不蒜子统计PV -->
        <span id="busuanzi_container_site_pv" style="display: none">
            总访问量 
            <span id="busuanzi_value_site_pv"></span>
             次
          </span>
      
      
        <!-- 不蒜子统计UV -->
        <span id="busuanzi_container_site_uv" style="display: none">
            总访客数 
            <span id="busuanzi_value_site_uv"></span>
             人
          </span>
      
    
  </div>


    

    
  </div>
</footer>

<!-- SCRIPTS -->
<script  src="https://cdn.staticfile.org/jquery/3.4.1/jquery.min.js" ></script>
<script  src="https://cdn.staticfile.org/twitter-bootstrap/4.4.1/js/bootstrap.min.js" ></script>
<script  src="/js/debouncer.js" ></script>
<script  src="/js/main.js" ></script>

<!-- Plugins -->


  
    <script  src="/js/lazyload.js" ></script>
  



  <script defer src="https://cdn.staticfile.org/clipboard.js/2.0.6/clipboard.min.js" ></script>
  <script  src="/js/clipboard-use.js" ></script>



  <script defer src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" ></script>





  <script  src="https://cdn.staticfile.org/tocbot/4.11.1/tocbot.min.js" ></script>
  <script>
    $(document).ready(function () {
      var boardCtn = $('#board-ctn');
      var boardTop = boardCtn.offset().top;

      tocbot.init({
        tocSelector: '#tocbot',
        contentSelector: 'article.markdown-body',
        headingSelector: 'h1,h2,h3,h4,h5,h6',
        linkClass: 'tocbot-link',
        activeLinkClass: 'tocbot-active-link',
        listClass: 'tocbot-list',
        isCollapsedClass: 'tocbot-is-collapsed',
        collapsibleClass: 'tocbot-is-collapsible',
        collapseDepth: 0,
        scrollSmooth: true,
        headingsOffset: -boardTop
      });
      if ($('.toc-list-item').length > 0) {
        $('#toc').css('visibility', 'visible');
      }
    });
  </script>



  <script  src="https://cdn.staticfile.org/typed.js/2.0.11/typed.min.js" ></script>
  <script>
    var typed = new Typed('#subtitle', {
      strings: [
        '  ',
        "MySQL高级&nbsp;",
      ],
      cursorChar: "_",
      typeSpeed: 70,
      loop: false,
    });
    typed.stop();
    $(document).ready(function () {
      $(".typed-cursor").addClass("h2");
      typed.start();
    });
  </script>



  <script  src="https://cdn.staticfile.org/anchor-js/4.2.2/anchor.min.js" ></script>
  <script>
    anchors.options = {
      placement: "right",
      visible: "hover",
      
    };
    var el = "h1,h2,h3,h4,h5,h6".split(",");
    var res = [];
    for (item of el) {
      res.push(".markdown-body > " + item)
    }
    anchors.add(res.join(", "))
  </script>



  <script  src="/js/local-search.js" ></script>
  <script>
    var path = "/local-search.xml";
    var inputArea = document.querySelector("#local-search-input");
    inputArea.onclick = function () {
      searchFunc(path, 'local-search-input', 'local-search-result');
      this.onclick = null
    }
  </script>



  <script  src="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.js" ></script>
  <link  rel="stylesheet" href="https://cdn.staticfile.org/fancybox/3.5.7/jquery.fancybox.min.css" />

  <script>
    $('#post img:not(.no-zoom img, img[no-zoom]), img[zoom]').each(
      function () {
        var element = document.createElement('a');
        $(element).attr('data-fancybox', 'images');
        $(element).attr('href', $(this).attr('src'));
        $(this).wrap(element);
      }
    );
  </script>

















  
    <!-- Baidu Analytics -->
    <script defer>
      var _hmt = _hmt || [];
      (function () {
        var hm = document.createElement("script");
        hm.src = "https://hm.baidu.com/hm.js?ba41ec605b9b7320e120275462e4035b";
        var s = document.getElementsByTagName("script")[0];
        s.parentNode.insertBefore(hm, s);
      })();
    </script>
  

  

  

  

  

  





</body>
</html>
